import datetime
import os.path
import re
import time
import pymysql.converters
import chardet

from global_variables import GlobalVariables


class MysqlDumpSql:
    def __init__(self):
        self.version = "v0.1.0"
        self.fileHandler = None
        self.tables = []
        self.views = []
        self.triggers = []
        self.procedures = []
        self.functions = []
        self.events = []
        self.dbHandler = None
        self.tableColumnTypes = {}

        self.dumpSettingsDefault = {
            'include-tables': [],
            'exclude-tables': [],
            'include-views': [],
            'compress': 'None',
            'init_commands': [],
            'no-data': [],
            'if-not-exists': True,
            'reset-auto-increment': False,
            'add-drop-database': False,
            'add-drop-table': False,
            'add-drop-trigger': True,
            'add-locks': True,
            # insert语句有完整的映射关系，对应字段与值
            'complete-insert': True,
            'databases': False,
            'default-character-set': "utf8mb4",
            'disable-keys': True,
            # insert语句是否合并为一条语句
            'extended-insert': True,
            'events': False,
            'hex-blob': True,  # faster than escaped content
            'insert-ignore': False,
            'no-autocommit': True,
            'no-create-db': False,
            'no-create-info': False,
            'lock-tables': True,
            'routines': False,
            'single-transaction': True,
            'skip-triggers': False,
            'skip-tz-utc': False,
            'skip-comments': False,
            'skip-dump-date': False,
            'skip-definer': False,
            'where': '',
            # deprecated
            'disable-foreign-keys-check': True
        }

        self.dumpSettings = self.dumpSettingsDefault
        self.typeAdapter = TypeAdapterMysql(self)
        self.mysqldb = GlobalVariables.DATABASE_OBJ

    def set_export_table_list(self, table_list: list):
        self.dumpSettings['include-tables'] = [t for t in table_list]

    # * Import supplied SQL file
    # * @param string path Absolute path to imported *.sql file
    def restore(self, path):
        if len(path) == 0 or not os.path.isfile(path):
            raise Exception(f"File {path} does not exist.")
        with open(path, 'rb') as f:
            f_content = f.read(1024 * 1024)
        print(chardet.detect(f_content))

        with open(path, 'r') as f:
            buffer = ''
            line = f.readline()
            while len(line) > 0:
                line = line.strip()
                if line[0:2] == "--" or len(line) == 0:
                    line = f.readline()
                    continue
                buffer += line
                if line[-1:] == ";":
                    self.mysqldb.cursor_execute(buffer[:-1])
                    buffer = ''
                line = f.readline()

    def start(self, filename: str = ''):
        # Create output file
        self.open(filename)

        # Write some basic info to output file
        self.write(self.getDumpFileHeader())

        # initiate a transaction at global level to create a consistent snapshot
        if self.dumpSettings['single-transaction']:
            self.mysqldb.setup_transaction()
            self.mysqldb.start_transaction()

        # Store server settings and use saner defaults to dump
        self.write(self.typeAdapter.backup_parameters())

        # Get table, view, trigger, procedures, functions and events structures from
        # database.
        self.getDatabaseStructureTables()
        self.getDatabaseStructureViews()
        self.getDatabaseStructureTriggers()
        self.getDatabaseStructureProcedures()
        self.getDatabaseStructureFunctions()
        self.getDatabaseStructureEvents()

        self.exportTables()
        self.exportTriggers()
        self.exportFunctions()
        self.exportProcedures()
        self.exportViews()
        self.exportEvents()

        # Restore saved parameters.
        self.write(self.typeAdapter.restore_parameters())

        # end transaction
        if self.dumpSettings['single-transaction']:
            self.mysqldb.commit_transaction()

        # Write some stats to output file.
        self.write(self.getDumpFileFooter())
        # Close output file.
        self.close()

        return

    # Returns header for dump file.
    # @return string
    def getDumpFileHeader(self):
        header = ''
        if not self.dumpSettings['skip-comments']:
            # Some info about software, source and time
            header = f"-- sequel-mysql https://gitee.com/lihuababa/sequel-mysql" + os.linesep + \
                     f"--" + os.linesep + \
                     f"-- Host: {self.mysqldb.host}\tDatabase: {self.mysqldb.current_database()}" + os.linesep + \
                     "-- ------------------------------------------------------" + os.linesep

            if len(self.version) > 0:
                header += "-- Server version \t" + self.version + os.linesep

            if not self.dumpSettings['skip-dump-date']:
                header += "-- Date: " + time.strftime("%Y-%m-%d %H:%M:%S") + os.linesep + os.linesep
        return header

    # Returns footer for dump file.
    # @return string
    def getDumpFileFooter(self):
        footer = ''
        if self.dumpSettings['skip-comments']:
            footer += '-- Dump completed'
            if not self.dumpSettings['skip-dump-date']:
                footer += ' on: ' + time.strftime("%Y-%m-%d")
            footer += os.linesep

        return footer

    # Reads table names from database.
    # Fills self.tables array, so they will be dumped later.
    # @return None
    def getDatabaseStructureTables(self):
        # Listing all tables from database
        table_list = self.mysqldb.show_tables()
        if len(self.dumpSettings['include-tables']) == 0:
            # include all tables for now, blacklisting happens later
            for _, row in enumerate(table_list):
                self.tables.append(row)
        else:
            # include only the tables mentioned in include-tables
            print(table_list, self.dumpSettings['include-tables'])
            for _, row in enumerate(table_list):
                if row in self.dumpSettings['include-tables']:
                    self.tables.append(row)
                    del self.dumpSettings['include-tables'][self.tables.index(row)]
        return

    # Reads view names from database.
    # Fills self.tables array, so they will be dumped later.
    # @return None
    def getDatabaseStructureViews(self):
        # Listing all views from database
        table_list = self.mysqldb.show_views("")
        if len(self.dumpSettings['include-views']) == 0:
            # include all views for now, blacklisting happens later
            for _, row in enumerate(table_list):
                self.views.append(row[0])
        else:
            # include only the tables mentioned in include-tables
            for _, row in enumerate(table_list):
                if row[0] in self.dumpSettings['include-views']:
                    self.views.append(row[0])
                    del self.dumpSettings['include-tables'][self.views.index(row[0])]
        return

    # Reads trigger names from database.
    # Fills self.tables array, so they will be dumped later.
    # @return None
    def getDatabaseStructureTriggers(self):
        # Listing all triggers from database
        table_list = self.mysqldb.show_triggers("")
        if not self.dumpSettings['skip-triggers']:
            for _, row in enumerate(table_list):
                self.triggers.append(row[0])
        return

    # Reads procedure names from database.
    # Fills self.tables array, so they will be dumped later.
    # @return None
    def getDatabaseStructureProcedures(self):
        # Listing all procedures from database
        table_list = self.mysqldb.show_procedure("")
        if self.dumpSettings['routines']:
            for _, row in enumerate(table_list):
                self.procedures.append(row[0])
        return

    # defs names from database.
    # Fills self.tables array, so they will be dumped later.
    # @return None
    def getDatabaseStructureFunctions(self):
        # Listing defs from database
        table_list = self.mysqldb.show_function("")
        if self.dumpSettings['routines']:
            for _, row in enumerate(table_list):
                self.functions.append(row["def_name"])
        return

    # Reads event names from database.
    # Fills self.tables array, so they will be dumped later.
    # @return None
    def getDatabaseStructureEvents(self):
        # Listing all events from database
        table_list = self.mysqldb.show_events("")
        if self.dumpSettings['events']:
            for _, row in enumerate(table_list):
                self.events.append(row[0])
        return

    # Exports all the tables selected from database
    # @return None
    def exportTables(self):
        # Exporting tables one by one
        for _, table in enumerate(self.tables):
            self.getTableStructure(table)
            if not self.dumpSettings['no-data']:
                # don't break compatibility with old trigger
                self.listValues(table)
            elif self.dumpSettings['no-data']:
                continue
            else:
                self.listValues(table)

    # Exports all the views found in database
    # @return None
    def exportViews(self):
        if not self.dumpSettings['no-create-info']:
            # Exporting views one by one
            for _, view in enumerate(self.views):
                self.tableColumnTypes[view] = self.getTableColumnTypes(view)
                self.getViewStructureTable(view)
            for _, view in enumerate(self.views):
                self.getViewStructureView(view)

    # Exports all the triggers found in database
    # @return None
    def exportTriggers(self):
        # Exporting triggers one by one
        for _, trigger in enumerate(self.triggers):
            self.getTriggerStructure(trigger)

    # Exports all the procedures found in database
    # @return None
    def exportProcedures(self):
        # Exporting triggers one by one
        for _, procedure in enumerate(self.procedures):
            self.getProcedureStructure(procedure)

    # Exports all defs found in database
    # @return None
    def exportFunctions(self):
        # Exporting triggers one by one
        for _, function in enumerate(self.functions):
            self.getFunctionStructure(function)

    # Exports all the events found in database
    # @return None
    def exportEvents(self):
        # Exporting triggers one by one
        for _, event in enumerate(self.events):
            self.getEventStructure(event)

    # Table structure extractor
    # @param string tableName  Name of table to export
    # @return None
    def getTableStructure(self, tableName):
        if not self.dumpSettings['no-create-info']:
            ret = ''
            if not self.dumpSettings['skip-comments']:
                ret = "--" + os.linesep + \
                      f"-- Table structure for table `{tableName}`" + os.linesep + \
                      "--" + os.linesep + os.linesep
            result = self.mysqldb.show_create_table(tableName)
            self.write(ret)
            if self.dumpSettings['add-drop-table']:
                self.write(self.typeAdapter.drop_table(tableName))
            self.write(self.typeAdapter.create_table(result))
        self.tableColumnTypes[tableName] = self.getTableColumnTypes(tableName)
        return

    # Store column types to create data dumps and for Stand-In tables
    # @param string tableName  Name of table to export
    # @return array type column types detailed

    def getTableColumnTypes(self, tableName):
        columns = self.mysqldb.show_columns_from_table(tableName)
        columnTypes = {}
        for _, col in enumerate(columns):
            types = self.typeAdapter.parseColumnType(col)
            columnTypes[col['Field']] = {
                'is_numeric': types['is_numeric'],
                'is_blob': types['is_blob'],
                'type': types['type'],
                'type_sql': col['Type'],
                'is_virtual': types['is_virtual']
            }

        return columnTypes

    # View structure extractor, create table (avoids cyclic references)
    # @param string viewName  Name of view to export
    # @return None
    def getViewStructureTable(self, viewName):
        if not self.dumpSettings['skip-comments']:
            ret = "--" + os.linesep + f"-- Stand-In structure for view `{viewName}`" + os.linesep +\
                  "--" + os.linesep + os.linesep
            self.write(ret)
        stmt = self.typeAdapter.show_create_view(viewName)

        # create views as tables, to resolve dependencies
        for _, r in enumerate(self.dbHandler.query(stmt)):
            if self.dumpSettings['add-drop-table']:
                self.write(self.typeAdapter.drop_view(viewName))

            self.write(self.createStandInTable(viewName))
            break

    # Write a create table statement for the table Stand-In, show create
    # table would return a create algorithm when used on a view
    # @param string viewName  Name of view to export
    # @return string create statement
    def createStandInTable(self, viewName):
        ret = []
        for k, v in self.tableColumnTypes[viewName].items():
            ret.append(f"`{k}` {v['type_sql']}")
        ret = os.linesep + ",".join(ret)
        ret = f"CREATE TABLE IF NOT EXISTS `{viewName}` (" + os.linesep + ret + os.linesep + ");" + os.linesep

        return ret

    # View structure extractor, create view
    # @param string viewName  Name of view to export
    # @return None
    def getViewStructureView(self, viewName):
        if not self.dumpSettings['skip-comments']:
            ret = "--" + os.linesep + f"-- View structure for view `{viewName}`" + os.linesep + \
                  "--" + os.linesep + os.linesep
            self.write(ret)
        stmt = self.typeAdapter.show_create_view(viewName)

        # create views, to resolve dependencies
        # replacing tables with views
        for _, r in enumerate(self.dbHandler.query(stmt)):
            self.write(
                self.typeAdapter.drop_view(viewName)
            )
            self.write(
                self.typeAdapter.create_view(r)
            )
            break

    # Trigger structure extractor
    # @param string triggerName  Name of trigger to export
    # @return None
    def getTriggerStructure(self, triggerName):
        stmt = self.typeAdapter.show_create_trigger(triggerName)
        for _, r in enumerate(self.dbHandler.query(stmt)):
            if self.dumpSettings['add-drop-trigger']:
                self.write(self.typeAdapter.add_drop_trigger(triggerName))
            self.write(
                self.typeAdapter.create_trigger(r)
            )
            return

    # Procedure structure extractor
    # @param string procedureName  Name of procedure to export
    # @return None
    def getProcedureStructure(self, procedureName):
        if not self.dumpSettings['skip-comments']:
            ret = "--" + os.linesep + "-- Dumping routines for database '" + self.dbName + "'" + os.linesep + \
                  "--" + os.linesep + os.linesep
            self.write(ret)
        stmt = self.typeAdapter.show_create_procedure(procedureName)
        for _, r in enumerate(self.dbHandler.query(stmt)):
            self.write(
                self.typeAdapter.create_procedure(r)
            )

    # Function structure extractor
    # @param defName  Name def to export
    # @return None
    def getFunctionStructure(self, defName):
        if not self.dumpSettings['skip-comments']:
            ret = "--" + os.linesep + "-- Dumping routines for database '" + self.dbName + "'" + os.linesep + \
                  "--" + os.linesep + os.linesep
            self.write(ret)
        stmt = self.typeAdapter.show_create_function(defName)
        for _, r in enumerate(self.dbHandler.query(stmt)):
            self.write(self.typeAdapter.create_function(r))
            return

    # Event structure extractor
    # @param string eventName  Name of event to export
    # @return None
    def getEventStructure(self, eventName):
        if not self.dumpSettings['skip-comments']:
            ret = "--" + os.linesep + "-- Dumping events for database '" + self.dbName + "'" + os.linesep + \
                  "--" + os.linesep + os.linesep
            self.write(ret)
        stmt = self.typeAdapter.show_create_event(eventName)
        for _, r in enumerate(self.dbHandler.query(stmt)):
            self.write(self.typeAdapter.create_event(r))
            return

    # Prepare values for output
    # @param string tableName Name of table which contains rows
    # @param row: list Associative array of column names and values to be
    #  quoted
    # @return array
    def prepareColumnValues(self, tableName, row: dict):
        ret = []
        columnTypes = self.tableColumnTypes[tableName]

        for colName, colValue in row.items():
            ret.append(self.escape(colValue, columnTypes[colName]))

        return ret

    # Escape values with quotes when needed
    # @param string tableName Name of table which contains rows
    # @param row: list Associative array of column names and values to be quoted
    # @return string
    def escape(self, colValue, colType):
        if colValue is None:
            return "NULL"
        elif self.dumpSettings['hex-blob'] and colType['is_blob']:
            if colType['type'] == 'bit' or len(colValue) > 0:
                return f"0x{colValue}"
            else:
                return "''"
        elif colType['is_numeric']:
            return colValue
        elif isinstance(colValue, datetime.datetime):
            return colValue.strftime("%Y-%m-%d %H:%M:%S")
        return pymysql.converters.escape_string(colValue)

    # Table rows extractor
    # @param string tableName  Name of table to export
    # @return None
    def listValues(self, tableName):
        self.prepareListValues(tableName)
        colNames = self.getColumnNames(tableName)

        offset = 0
        limit = 1000

        while True:
            count, resultSet = self.mysqldb.table_content(tableName, offset=offset, limit=limit)
            if len(resultSet) == 0:
                break
            ignore = ' IGNORE' if self.dumpSettings['insert-ignore'] else ''
            count = 0
            sql = ""
            for key, row in enumerate(resultSet):
                count += 1
                vals = self.prepareColumnValues(tableName, row)
                values = ",".join([str(v) if isinstance(v, int) else f"'{v}'" for _, v in enumerate(vals)])
                if key == 0:
                    if self.dumpSettings['complete-insert']:
                        sql += f"INSERT{ignore} INTO `{tableName}` (" + ", ".join(colNames) + \
                               f") VALUES ({values})"
                    else:
                        sql += f"INSERT{ignore} INTO `{tableName}` VALUES ({values})"
                else:
                    if self.dumpSettings['extended-insert']:
                        sql += f",{os.linesep}({values})"
                    else:
                        sql += ";" + os.linesep
                        if self.dumpSettings['complete-insert']:
                            sql += f"INSERT{ignore} INTO `{tableName}` (" + ", ".join(list(row.values())) + \
                                   f") VALUES ({values})"
                        else:
                            sql += f"INSERT{ignore} INTO `{tableName}` VALUES ({values})"
            sql += ";" + os.linesep * 2
            self.write(sql)
            offset = offset + limit

        self.endListValues(tableName, count)

    # Table rows extractor, append information prior to dump
    # @param string tableName  Name of table to export
    # @return None
    def prepareListValues(self, tableName):
        if not self.dumpSettings['skip-comments']:
            self.write(
                "--" + os.linesep +
                f"-- Dumping data for table `{tableName}`" + os.linesep +
                "--" + os.linesep + os.linesep
            )

        if self.dumpSettings['lock-tables'] and not self.dumpSettings['single-transaction']:
            self.mysqldb.lock_table(tableName)

        if self.dumpSettings['add-locks']:
            self.write(self.typeAdapter.start_add_lock_table(tableName))

        if self.dumpSettings['disable-keys']:
            self.write(self.typeAdapter.start_add_disable_keys(tableName))

        # Disable autocommit for faster reload
        if self.dumpSettings['no-autocommit']:
            self.write(self.typeAdapter.start_disable_autocommit())

        return

    # Table rows extractor, close locks and commits after dump
    # @param string tableName Name of table to export.
    # @param integer    count     Number of rows inserted.
    # @return void
    def endListValues(self, tableName, count=0):
        if self.dumpSettings['disable-keys']:
            self.write(self.typeAdapter.end_add_disable_keys(tableName))

        if self.dumpSettings['add-locks']:
            self.write("UNLOCK TABLES;" + os.linesep)

        if self.dumpSettings['lock-tables'] and not self.dumpSettings['single-transaction']:
            self.mysqldb.unlock_table()

        # Commit to enable autocommit
        if self.dumpSettings['no-autocommit']:
            self.write(self.typeAdapter.end_disable_autocommit())

        self.write(os.linesep)

        if not self.dumpSettings['skip-comments']:
            self.write(
                f"-- Dumped table `{tableName}` with {count} row(s)" + os.linesep +
                '--' + os.linesep + os.linesep
            )
        return

    # Build SQL List of all columns on current table which will be used for inserting
    # @param string tableName  Name of table to get columns
    # @return array columns for sql sentence for insert
    def getColumnNames(self, tableName):
        colNames = []
        for colName, colType in self.tableColumnTypes[tableName].items():
            if colType['is_virtual']:
                self.dumpSettings['complete-insert'] = True
            else:
                colNames.append(f"`{colName}`")
        return colNames

    def open(self, filename: str):
        self.fileHandler = open(filename, "wb")
        return True

    def write(self, string: str):
        self.fileHandler.write(string.encode(encoding="utf-8"))

    def close(self):
        return self.fileHandler.close()


class TypeAdapterMysql:
    def __init__(self, dump):

        self.DEFINER_RE = 'DEFINER=`(?:[^`]|``)*`@`(?:[^`]|``)*`'

        # Numerical Mysql types
        self.mysqlTypes = {
            'numerical': [
                'bit',
                'tinyint',
                'smallint',
                'mediumint',
                'int',
                'integer',
                'bigint',
                'real',
                'double',
                'float',
                'decimal',
                'numeric'
            ],
            'blob': [
                'tinyblob',
                'blob',
                'mediumblob',
                'longblob',
                'binary',
                'varbinary',
                'bit',
                'geometry',  # /* http://bugs.mysql.com/bug.php?id=43544 */
                'point',
                'linestring',
                'polygon',
                'multipoint',
                'multilinestring',
                'multipolygon',
                'geometrycollection',
            ],
        }
        self.dumpSettings = dump.dumpSettings

    def show_create_table(self, tableName):
        return f"SHOW CREATE TABLE `{tableName}`"

    def show_create_view(self, viewName):
        return f"SHOW CREATE VIEW `{viewName}`"

    def show_create_trigger(self, triggerName):
        return f"SHOW CREATE TRIGGER `{triggerName}`"

    def show_create_procedure(self, procedureName):
        return f"SHOW CREATE PROCEDURE `{procedureName}`"

    def show_create_function(self, defName):
        return f"SHOW CREATE FUNCTION {defName}`"

    def show_create_event(self, eventName):
        return f"SHOW CREATE EVENT `{eventName}`"

    def create_table(self, createTable):
        createTable = createTable['Create Table']
        if self.dumpSettings['reset-auto-increment']:
            match = "/AUTO_INCREMENT=[0-9]+/s"
            replace = ""
            createTable = re.sub(match, replace, createTable)

        if self.dumpSettings['if-not-exists']:
            createTable = re.sub('/^CREATE TABLE/', 'CREATE TABLE IF NOT EXISTS', createTable)

        ret = "/*!40101 SET @saved_cs_client = @@character_set_client */;" + os.linesep + \
              "/*!40101 SET character_set_client = " + self.dumpSettings[
                  'default-character-set'] + " */;" + os.linesep + \
              createTable + ";" + os.linesep + \
              "/*!40101 SET character_set_client = @saved_cs_client */;" + os.linesep + os.linesep
        return ret

    def create_view(self, row):
        ret = ""
        if row.get('Create View') is None:
            raise Exception("Error getting view structure, unknown output")

        viewStmt = row['Create View']

        definerStr = '' if self.dumpSettings['skip-definer'] else r'/*!50013 \2 */' + os.linesep
        viewStmtReplaced = re.sub(
            r'/^(CREATE(?:\s+ALGORITHM=(?:UNDEFINED|MERGE|TEMPTABLE))?)\s+(' + self.DEFINER_RE + '(?:\s+SQL SECURITY (?:DEFINER|INVOKER))?)?\s+(VIEW .+)$/',
            '/*!50001 \1 */' + os.linesep + definerStr + '/*!50001 \3 */', viewStmt)
        if viewStmtReplaced:
            viewStmt = viewStmtReplaced

        ret += viewStmt + ';' + os.linesep + os.linesep
        return ret

    def create_trigger(self, row):
        ret = ""
        if row.get('SQL Original Statement') is None:
            raise Exception("Error getting trigger code, unknown output")

        triggerStmt = row['SQL Original Statement']
        definerStr = "" if self.dumpSettings['skip-definer'] else r'/*!50017 \2*/ '
        triggerStmtReplaced = re.sub(
            '/^(CREATE)\s+(' + self.DEFINER_RE + ')?\s+(TRIGGER\s.*)$/s',
            r'/*!50003 \1*/ ' + definerStr + r'/*!50003 \3 */',
            triggerStmt,
        )
        if triggerStmtReplaced:
            triggerStmt = triggerStmtReplaced

        ret += "DELIMITER ;;" + os.linesep + triggerStmt + ";;" + os.linesep + "DELIMITER ;" + os.linesep + os.linesep
        return ret

    def create_procedure(self, row):
        ret = ""
        if row.get('Create Procedure') is None:
            raise Exception("Error getting procedure code, unknown output. "
                            "Please check 'https://bugs.mysql.com/bug.php?id=14564'")
        procedureStmt = row['Create Procedure']
        if self.dumpSettings['skip-definer']:
            procedureStmtReplaced = re.sub(
                r'/^(CREATE)\s+(' + self.DEFINER_RE + ')?\s+(PROCEDURE\s.*)$/s',
                r'\1 \3',
                procedureStmt
            )
            if procedureStmtReplaced:
                procedureStmt = procedureStmtReplaced

        ret += "/*!50003 DROP PROCEDURE IF EXISTS `" + \
               row['Procedure'] + "` */;" + os.linesep + \
               "/*!40101 SET @saved_cs_client     = @@character_set_client */;" + os.linesep + \
               "/*!40101 SET character_set_client = " + self.dumpSettings[
                   'default-character-set'] + " */;" + os.linesep + \
               "DELIMITER ;;" + os.linesep + \
               procedureStmt + " ;;" + os.linesep + \
               "DELIMITER ;" + os.linesep + \
               "/*!40101 SET character_set_client = @saved_cs_client */;" + os.linesep + os.linesep

        return ret

    def create_function(self, row):
        ret = ""
        if row.get('Create Function') is None:
            raise Exception("Error def code, unknown output. " +
                            "Please check 'https://bugs.mysql.com/bug.php?id=14564'")
        defStmt = row['Create Function']
        characterSetClient = row['character_set_client']
        collationConnection = row['collation_connection']
        sqlMode = row['sql_mode']
        if self.dumpSettings['skip-definer']:
            defStmtReplaced = re.sub(
                r'/^(CREATE)\s+(' + self.DEFINER_RE + r')?\s+(FUNCTION\s.*)$/s',
                r'\1 \3',
                defStmt,
                1
            )
            if defStmtReplaced:
                defStmt = defStmtReplaced

        ret += "/*!50003 DROP FUNCTION IF EXISTS `" + \
               row['Function'] + "` */;" + os.linesep + \
               "/*!40101 SET @saved_cs_client     = @@character_set_client */;" + os.linesep + \
               "/*!50003 SET @saved_cs_results     = @@character_set_results */ ;" + os.linesep + \
               "/*!50003 SET @saved_col_connection = @@collation_connection */ ;" + os.linesep + \
               "/*!40101 SET character_set_client = " + characterSetClient + " */;" + os.linesep + \
               "/*!40101 SET character_set_results = " + characterSetClient + " */;" + os.linesep + \
               "/*!50003 SET collation_connection  = " + collationConnection + " */ ;" + os.linesep + \
               "/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;" + os.linesep + \
               "/*!50003 SET sql_mode              = '" + sqlMode + "' */ ;;" + os.linesep + \
               "/*!50003 SET @saved_time_zone      = @@time_zone */ ;;" + os.linesep + \
               "/*!50003 SET time_zone             = 'SYSTEM' */ ;;" + os.linesep + \
               "DELIMITER ;;" + os.linesep + \
               defStmt + " ;;" + os.linesep + \
               "DELIMITER ;" + os.linesep + \
               "/*!50003 SET sql_mode              = @saved_sql_mode */ ;" + os.linesep + \
               "/*!50003 SET character_set_client  = @saved_cs_client */ ;" + os.linesep + \
               "/*!50003 SET character_set_results = @saved_cs_results */ ;" + os.linesep + \
               "/*!50003 SET collation_connection  = @saved_col_connection */ ;" + os.linesep + \
               "/*!50106 SET TIME_ZONE= @saved_time_zone */ ;" + os.linesep + os.linesep

        return ret

    def create_event(self, row):
        ret = ""
        if row.get('Create Event') is None:
            raise Exception(
                "Error getting event code, unknown output. Please check 'http://stackoverflow.com/questions"
                "/10853826/mysql-5-5-create-event-gives-syntax-error'")
        eventName = row['Event']
        eventStmt = row['Create Event']
        sqlMode = row['sql_mode']
        definerStr = "" if self.dumpSettings['skip-definer'] else r'/*!50117 \2*/ '
        eventStmtReplaced = re.sub(
            r'/^(CREATE)\s+(' + self.DEFINER_RE + ')?\s+(EVENT .*)$/',
            r'/*!50106 \1*/ ' + definerStr + '/*!50106 \3 */',
            eventStmt,
            1
        )
        if eventStmtReplaced:
            eventStmt = eventStmtReplaced

        ret += "/*!50106 SET @save_time_zone= @@TIME_ZONE */ ;" + os.linesep + \
               "/*!50106 DROP EVENT IF EXISTS `" + eventName + "` */;" + os.linesep + \
               "DELIMITER ;;" + os.linesep + \
               "/*!50003 SET @saved_cs_client      = @@character_set_client */ ;;" + os.linesep + \
               "/*!50003 SET @saved_cs_results     = @@character_set_results */ ;;" + os.linesep + \
               "/*!50003 SET @saved_col_connection = @@collation_connection */ ;;" + os.linesep + \
               "/*!50003 SET character_set_client  = utf8 */ ;;" + os.linesep + \
               "/*!50003 SET character_set_results = utf8 */ ;;" + os.linesep + \
               "/*!50003 SET collation_connection  = utf8_general_ci */ ;;" + os.linesep + \
               "/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;" + os.linesep + \
               "/*!50003 SET sql_mode              = '" + sqlMode + "' */ ;;" + os.linesep + \
               "/*!50003 SET @saved_time_zone      = @@time_zone */ ;;" + os.linesep + \
               "/*!50003 SET time_zone             = 'SYSTEM' */ ;;" + os.linesep + \
               eventStmt + " ;;" + os.linesep + \
               "/*!50003 SET time_zone             = @saved_time_zone */ ;;" + os.linesep + \
               "/*!50003 SET sql_mode              = @saved_sql_mode */ ;;" + os.linesep + \
               "/*!50003 SET character_set_client  = @saved_cs_client */ ;;" + os.linesep + \
               "/*!50003 SET character_set_results = @saved_cs_results */ ;;" + os.linesep + \
               "/*!50003 SET collation_connection  = @saved_col_connection */ ;;" + os.linesep + \
               "DELIMITER ;" + os.linesep + \
               "/*!50106 SET TIME_ZONE= @save_time_zone */ ;" + os.linesep + os.linesep
        # Commented because we are doing this in restore_parameters()
        # "/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;" . os.linesep . os.linesep

        return ret

    def start_add_lock_table(self, *args):
        return f"LOCK TABLES `{args[0]}` WRITE;" + os.linesep

    def start_add_disable_keys(self, *args):
        return f"/*!40000 ALTER TABLE `{args[0]}` DISABLE KEYS */;" + os.linesep

    def end_add_disable_keys(self, *args):
        return f"/*!40000 ALTER TABLE `{args[0]}` ENABLE KEYS */;" + os.linesep

    def start_disable_autocommit(self):
        return "SET autocommit=0;" + os.linesep

    def end_disable_autocommit(self):
        return "COMMIT;" + os.linesep

    def add_drop_trigger(self, *args):
        return f"DROP TRIGGER IF EXISTS `{args[0]}`;" + os.linesep

    def drop_table(self, *args):
        return f"DROP TABLE IF EXISTS `{args[0]}`;" + os.linesep

    def drop_view(self, *args):
        return f"DROP TABLE IF EXISTS `{args[0]}`;" + os.linesep + \
               "/*!50001 DROP VIEW IF EXISTS `{args[0]}`*/;" + os.linesep

    # Decode column metadata and fill info structure.
    # type, is_numeric and is_blob will always be available.
    # @param colType: list Array returned from "SHOW COLUMNS FROM tableName"
    # @return array
    def parseColumnType(self, col):
        column_parts = col.get('Type', '').split(" ")
        column_parts_0 = column_parts[0]
        if column_parts_0.count('(') > 0:
            c_type = column_parts_0[:column_parts_0.index('(')]
            length = column_parts_0[column_parts_0.index('(') + 1:column_parts_0.index(')')]
        else:
            c_type = column_parts_0
            length = 0
        attributes = column_parts[1] if len(column_parts) >= 2 else None
        colInfo = {
            "type": c_type,
            "length": int(length),
            "attributes": attributes,
            "is_numeric": c_type in self.mysqlTypes['numerical'],
            "is_blob": c_type in self.mysqlTypes['blob'],
            "is_virtual": col['Extra'].count("VIRTUAL GENERATED") > 0 or col['Extra'].count("STORED GENERATED") > 0
        }
        return colInfo

    def backup_parameters(self):
        ret = "/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;" + os.linesep + \
              "/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;" + os.linesep + \
              "/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;" + os.linesep + \
              "/*!40101 SET NAMES " + self.dumpSettings['default-character-set'] + " */;" + os.linesep

        if not self.dumpSettings['skip-tz-utc']:
            ret += "/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;" + os.linesep + \
                   "/*!40103 SET TIME_ZONE='+00:00' */;" + os.linesep

        if self.dumpSettings['no-autocommit']:
            ret += "/*!40101 SET @OLD_AUTOCOMMIT=@@AUTOCOMMIT */;" + os.linesep

        ret += "/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;" + os.linesep + \
               "/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;" + os.linesep + \
               "/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;" + os.linesep + \
               "/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;" + os.linesep + os.linesep

        return ret

    def restore_parameters(self):
        ret = ""

        if not self.dumpSettings['skip-tz-utc']:
            ret += "/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;" + os.linesep

        if self.dumpSettings['no-autocommit']:
            ret += "/*!40101 SET AUTOCOMMIT=@OLD_AUTOCOMMIT */;" + os.linesep

        ret += "/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;" + os.linesep + \
               "/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;" + os.linesep + \
               "/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;" + os.linesep + \
               "/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;" + os.linesep + \
               "/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;" + os.linesep + \
               "/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;" + os.linesep + \
               "/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;" + os.linesep + os.linesep

        return ret
